﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BusinessLogic;
using System.Data.SqlClient;
using System.Data;
using System.Web.Security;

namespace DataAccessLogic
{
    public static class EmailDataAccess
    {
        #region Methods
        private static Email BindRowToEmail(DataRow row)
        {            
            Email email = null;
            
            if(row != null)
            {
                email = new Email();
                email.EmailAddress = DataAccess.CheckForStringColumn(row, "EMAILADDRESS");
                email.IsActive = DataAccess.CheckForBooleanColumn(row, "ISACTIVE");
                email.IsPrimary = DataAccess.CheckForBooleanColumn(row, "ISPRIMARY");
                email.ID = DataAccess.CheckForGuidColumn(row, "ID");
                email.DateAdded = DataAccess.CheckForDateTimeColumn(row, "DATEADDED");
                email.DateChanged = DataAccess.CheckForDateTimeColumn(row, "DATECHANGED");
                email.AddedByID = DataAccess.CheckForGuidColumn(row, "ADDEDBYID");
                email.ChangedByID = DataAccess.CheckForGuidColumn(row, "CHANGEDBYID");

                EmailType et = new EmailType();
                et.ID = DataAccess.CheckForGuidColumn(row, "EMAILADDRESSTYPECODEID");
                et.Description = DataAccess.CheckForStringColumn(row, "EMAILADDRESSTYPE");

                email.EmailAddressType = et;
            }

            return email;
        }

        public static Guid CreateForConversion(Email email, Guid constituentID)
        {
            return Create(email, constituentID, true);
        }

        public static Guid Create(Email email, Guid constituentID)
        {
            return Create(email, constituentID, false);
        }

        public static Guid Create(Email email, Guid constituentID, bool isConversion)
        {
            Guid id = Guid.Empty;
            Guid providerUserKey = new Guid();

            try
            {
                providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());
            }
            catch
            {
                providerUserKey = new Guid("B6E747BD-F3D5-482A-9785-9FEF877D5B6C");
            }

            if (isConversion)
            {
                providerUserKey = new Guid("c9014ff9-ee89-4192-aa6b-b0c2130557ba");
            }

            string outputParameterName = "@ID";
            
            if ((email != null) && (constituentID != Guid.Empty))
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                SqlParameter p = new SqlParameter();
                p.ParameterName = outputParameterName;
                p.DbType = System.Data.DbType.Guid;
                p.Direction = System.Data.ParameterDirection.Output;

                parms.Add(p);
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));
                parms.Add(new SqlParameter("@EMAILADDRESSTYPECODEID", email.EmailAddressType.ID));
                parms.Add(new SqlParameter("@EMAILADDRESS", email.EmailAddress));
                parms.Add(new SqlParameter("@ISPRIMARY", email.IsPrimary));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                object obj = DataAccess.Create(StoredProcedures.USP_EMAILADDRESS_CREATE, outputParameterName, parms);

                if (obj != null)
                {
                    id = new Guid(obj.ToString());
                }
            }

            return id;
        }

        public static void Delete(Guid id)
        {
            if(id != Guid.Empty)
            {
                DataAccess.Delete(StoredProcedures.USP_EMAILADDRESS_DELETE, id);
            }
        }

        public static void DeleteByConstituentID(Guid constituentID)
        {
            if(constituentID != Guid.Empty)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                DataAccess.Delete(StoredProcedures.USP_EMAILADDRESS_DELETEBYCONSTITUENTID, parms);
            }
        }

        private static List<Email> Get(string storedProcedure, Guid id, string parameterName)
        {
            List<Email> emails = null;

            if(id != Guid.Empty)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter(parameterName, id));

                DataTable dt = DataAccess.Get(storedProcedure, parms);

                if (0 < dt.Rows.Count)
                {
                    emails = new List<Email>();

                    foreach (DataRow row in dt.Rows)
                    {
                        emails.Add(BindRowToEmail(row));
                    }
                }
            }

            return emails;
        }

        public static List<Email> GetActiveByConstituentID(Guid constituentID)
        {
            return Get(StoredProcedures.USP_EMAILADDRESS_GETACTIVEBYCONSTITUENTID, constituentID, "@CONSTITUENTID");
        }

        public static List<Email> GetAllByConstituentID(Guid constituentID)
        {
            return Get(StoredProcedures.USP_EMAILADDRESS_GETALLBYCONSTITUENTID, constituentID, "@CONSTITUENTID");
        }        

        public static Email GetByID(Guid id)
        {
            Email e = null;

            List<Email> emails = Get(StoredProcedures.USP_EMAILADDRESS_GETBYEMAILADDRESSID, id, "@EMAILADDRESSID");

            if (emails != null && 0 < emails.Count)
            {
                e = emails[0];
            }

            return e;
        }

        public static Email GetPrimaryByConstituentID(Guid constituentID)
        {
            List<Email> emails = Get(StoredProcedures.USP_EMAILADDRESS_GETPRIMARYBYCONSTITUENTID, constituentID, "@CONSTITUENTID");
            Email e = null;
            
            if (emails != null && 0 < emails.Count)
            {
                e = emails[0];                
            }

            return e;
        }

        public static void Update(Email email, Guid constituentID)
        {
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());
                        
            if ((email != null) && (constituentID != Guid.Empty))
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", email.ID));
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));
                parms.Add(new SqlParameter("@EMAILADDRESSTYPECODEID", email.EmailAddressType.ID));
                parms.Add(new SqlParameter("@EMAILADDRESS", email.EmailAddress));
                parms.Add(new SqlParameter("@ISPRIMARY", email.IsPrimary));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                DataAccess.Create(StoredProcedures.USP_EMAILADDRESS_UPDATE, parms);
            }
        }
        #endregion
    }
}